import { Pool, PoolClient } from 'pg';
import { Transaction, AddressLabel, MonitorConfig, AlertMessage, ChainType } from '../../types';
import { createModuleLogger } from '../../utils/logger';
import config from '../../config';

const logger = createModuleLogger('database-service');

export class DatabaseService {
  private pool: Pool;

  constructor() {
    this.pool = new Pool({
      host: config.database.host === 'localhost' ? '127.0.0.1' : config.database.host, // 强制使用IPv4
      port: config.database.port,
      database: config.database.database,
      user: config.database.username,
      password: config.database.password,
      ssl: config.database.ssl,
      // 添加连接选项
      max: 20, // 最大连接数
      idleTimeoutMillis: 30000, // 空闲超时
      connectionTimeoutMillis: 10000, // 连接超时
    });
  }

  // 初始化数据库表
  public async initializeTables(): Promise<void> {
    const client = await this.pool.connect();
    
    try {
      // 创建交易表
      await client.query(`
        CREATE TABLE IF NOT EXISTS transactions (
          id SERIAL PRIMARY KEY,
          hash VARCHAR(66) UNIQUE NOT NULL,
          chain VARCHAR(20) NOT NULL,
          from_address VARCHAR(42) NOT NULL,
          to_address VARCHAR(42) NOT NULL,
          value DECIMAL(36,18) NOT NULL,
          value_usd DECIMAL(15,2) NOT NULL,
          token_address VARCHAR(42),
          token_symbol VARCHAR(20),
          token_decimals INTEGER,
          block_number BIGINT NOT NULL,
          block_hash VARCHAR(66),
          transaction_index INTEGER,
          gas_used BIGINT,
          gas_price BIGINT,
          timestamp TIMESTAMP NOT NULL,
          from_label VARCHAR(100),
          to_label VARCHAR(100),
          created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
      `);

      // 创建地址标签表
      await client.query(`
        CREATE TABLE IF NOT EXISTS address_labels (
          id SERIAL PRIMARY KEY,
          address VARCHAR(42) NOT NULL,
          label VARCHAR(100) NOT NULL,
          type VARCHAR(20) NOT NULL,
          chain VARCHAR(20) NOT NULL,
          created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
          UNIQUE(address, chain)
        )
      `);

      // 创建告警消息表
      await client.query(`
        CREATE TABLE IF NOT EXISTS alert_messages (
          id SERIAL PRIMARY KEY,
          type VARCHAR(20) NOT NULL,
          title VARCHAR(200) NOT NULL,
          message TEXT NOT NULL,
          channel VARCHAR(20) NOT NULL,
          status VARCHAR(20) DEFAULT 'pending',
          sent_at TIMESTAMP,
          error TEXT,
          created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
      `);

      // 创建系统配置表
      await client.query(`
        CREATE TABLE IF NOT EXISTS system_config (
          id SERIAL PRIMARY KEY,
          config_key VARCHAR(100) UNIQUE NOT NULL,
          config_value TEXT NOT NULL,
          config_type VARCHAR(20) NOT NULL DEFAULT 'string',
          description TEXT,
          category VARCHAR(50) NOT NULL DEFAULT 'general',
          is_encrypted BOOLEAN DEFAULT FALSE,
          created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
          updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
      `);

      // 插入默认配置
      await this.insertDefaultConfigs(client);

      // 创建索引
      await client.query('CREATE INDEX IF NOT EXISTS idx_transactions_chain ON transactions(chain)');
      await client.query('CREATE INDEX IF NOT EXISTS idx_transactions_timestamp ON transactions(timestamp)');
      await client.query('CREATE INDEX IF NOT EXISTS idx_transactions_value_usd ON transactions(value_usd)');
      await client.query('CREATE INDEX IF NOT EXISTS idx_address_labels_address ON address_labels(address)');
      await client.query('CREATE INDEX IF NOT EXISTS idx_system_config_category ON system_config(category)');

      // ===== Analysis System Tables =====

      // Entity Information Table
      await client.query(`
        CREATE TABLE IF NOT EXISTS entities (
          id SERIAL PRIMARY KEY,
          entity_id VARCHAR(64) UNIQUE NOT NULL,
          name VARCHAR(200),
          type VARCHAR(50) NOT NULL,
          risk_score INTEGER DEFAULT 0,
          confidence DECIMAL(3,2) DEFAULT 0.00,
          metadata JSONB,
          created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
          updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
      `);

      // Entity Addresses Mapping Table
      await client.query(`
        CREATE TABLE IF NOT EXISTS entity_addresses (
          id SERIAL PRIMARY KEY,
          entity_id VARCHAR(64) NOT NULL,
          address VARCHAR(42) NOT NULL,
          chain VARCHAR(20) NOT NULL,
          confidence DECIMAL(3,2) DEFAULT 1.00,
          added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
          UNIQUE(entity_id, address, chain)
        )
      `);

      // Address Profiles Table
      await client.query(`
        CREATE TABLE IF NOT EXISTS address_profiles (
          id SERIAL PRIMARY KEY,
          address VARCHAR(42) NOT NULL,
          chain VARCHAR(20) NOT NULL,
          entity_id VARCHAR(64),
          risk_score INTEGER DEFAULT 0,
          total_transactions INTEGER DEFAULT 0,
          total_volume_usd DECIMAL(20,2) DEFAULT 0.00,
          first_seen TIMESTAMP,
          last_active TIMESTAMP,
          activity_score DECIMAL(5,2) DEFAULT 0.00,
          metadata JSONB,
          created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
          updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
          UNIQUE(address, chain)
        )
      `);

      // Transaction Patterns Table
      await client.query(`
        CREATE TABLE IF NOT EXISTS transaction_patterns (
          id SERIAL PRIMARY KEY,
          pattern_id VARCHAR(64) UNIQUE NOT NULL,
          address VARCHAR(42) NOT NULL,
          chain VARCHAR(20) NOT NULL,
          pattern_type VARCHAR(50) NOT NULL,
          confidence DECIMAL(3,2) NOT NULL,
          description TEXT,
          frequency INTEGER DEFAULT 1,
          avg_amount_usd DECIMAL(20,2),
          metadata JSONB,
          detected_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
          last_seen TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
      `);

      // Behavior Insights Table
      await client.query(`
        CREATE TABLE IF NOT EXISTS behavior_insights (
          id SERIAL PRIMARY KEY,
          insight_id VARCHAR(64) UNIQUE NOT NULL,
          address VARCHAR(42) NOT NULL,
          type VARCHAR(50) NOT NULL,
          title VARCHAR(200) NOT NULL,
          description TEXT,
          severity VARCHAR(20) DEFAULT 'low',
          confidence DECIMAL(3,2) NOT NULL,
          metadata JSONB,
          created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
      `);

      // Anomaly Alerts Table
      await client.query(`
        CREATE TABLE IF NOT EXISTS anomaly_alerts (
          id SERIAL PRIMARY KEY,
          alert_id VARCHAR(64) UNIQUE NOT NULL,
          address VARCHAR(42) NOT NULL,
          chain VARCHAR(20) NOT NULL,
          anomaly_type VARCHAR(50) NOT NULL,
          severity VARCHAR(20) NOT NULL,
          title VARCHAR(200) NOT NULL,
          description TEXT,
          threshold_value DECIMAL(20,2),
          actual_value DECIMAL(20,2),
          confidence DECIMAL(3,2) NOT NULL,
          status VARCHAR(20) DEFAULT 'active',
          metadata JSONB,
          detected_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
          resolved_at TIMESTAMP
        )
      `);

      // Market Impact Analysis Table
      await client.query(`
        CREATE TABLE IF NOT EXISTS market_impact_analysis (
          id SERIAL PRIMARY KEY,
          transaction_hash VARCHAR(66) NOT NULL,
          impact_score DECIMAL(5,2) NOT NULL,
          liquidity_impact DECIMAL(10,2),
          price_impact_percentage DECIMAL(8,4),
          volume_impact DECIMAL(20,2),
          market_sentiment VARCHAR(20),
          analysis_metadata JSONB,
          analyzed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
      `);

      // Intelligence Reports Table
      await client.query(`
        CREATE TABLE IF NOT EXISTS intelligence_reports (
          id SERIAL PRIMARY KEY,
          report_id VARCHAR(64) UNIQUE NOT NULL,
          title VARCHAR(300) NOT NULL,
          summary TEXT,
          content TEXT,
          report_type VARCHAR(50) NOT NULL,
          priority VARCHAR(20) DEFAULT 'medium',
          tags TEXT[],
          entities VARCHAR(64)[],
          addresses VARCHAR(42)[],
          chains VARCHAR(20)[],
          metadata JSONB,
          generated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
          expires_at TIMESTAMP
        )
      `);

      // Entity Relationships Table
      await client.query(`
        CREATE TABLE IF NOT EXISTS entity_relationships (
          id SERIAL PRIMARY KEY,
          source_entity_id VARCHAR(64) NOT NULL,
          target_entity_id VARCHAR(64) NOT NULL,
          relationship_type VARCHAR(50) NOT NULL,
          strength DECIMAL(3,2) DEFAULT 0.50,
          evidence_count INTEGER DEFAULT 1,
          first_observed TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
          last_observed TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
          metadata JSONB,
          UNIQUE(source_entity_id, target_entity_id, relationship_type)
        )
      `);

      // Pattern Detection Cache Table
      await client.query(`
        CREATE TABLE IF NOT EXISTS pattern_detection_cache (
          id SERIAL PRIMARY KEY,
          cache_key VARCHAR(128) UNIQUE NOT NULL,
          address VARCHAR(42) NOT NULL,
          chain VARCHAR(20) NOT NULL,
          pattern_data JSONB NOT NULL,
          expires_at TIMESTAMP NOT NULL,
          created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
      `);

      // Analysis Job Queue Table
      await client.query(`
        CREATE TABLE IF NOT EXISTS analysis_jobs (
          id SERIAL PRIMARY KEY,
          job_id VARCHAR(64) UNIQUE NOT NULL,
          job_type VARCHAR(50) NOT NULL,
          target_address VARCHAR(42),
          target_transaction VARCHAR(66),
          priority INTEGER DEFAULT 5,
          status VARCHAR(20) DEFAULT 'pending',
          progress INTEGER DEFAULT 0,
          result JSONB,
          error_message TEXT,
          attempts INTEGER DEFAULT 0,
          max_attempts INTEGER DEFAULT 3,
          created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
          started_at TIMESTAMP,
          completed_at TIMESTAMP
        )
      `);

      // Create additional indexes for existing tables
      await client.query('CREATE INDEX IF NOT EXISTS idx_transactions_from_address ON transactions(from_address)');
      await client.query('CREATE INDEX IF NOT EXISTS idx_transactions_to_address ON transactions(to_address)');

      // Create indexes for analysis tables
      await client.query('CREATE INDEX IF NOT EXISTS idx_entities_type ON entities(type)');
      await client.query('CREATE INDEX IF NOT EXISTS idx_entities_risk_score ON entities(risk_score)');
      await client.query('CREATE INDEX IF NOT EXISTS idx_entity_addresses_address ON entity_addresses(address)');
      await client.query('CREATE INDEX IF NOT EXISTS idx_entity_addresses_chain ON entity_addresses(chain)');
      await client.query('CREATE INDEX IF NOT EXISTS idx_address_profiles_address ON address_profiles(address, chain)');
      await client.query('CREATE INDEX IF NOT EXISTS idx_address_profiles_entity ON address_profiles(entity_id)');
      await client.query('CREATE INDEX IF NOT EXISTS idx_address_profiles_risk ON address_profiles(risk_score)');
      await client.query('CREATE INDEX IF NOT EXISTS idx_transaction_patterns_address ON transaction_patterns(address, chain)');
      await client.query('CREATE INDEX IF NOT EXISTS idx_transaction_patterns_type ON transaction_patterns(pattern_type)');
      await client.query('CREATE INDEX IF NOT EXISTS idx_behavior_insights_address ON behavior_insights(address)');
      await client.query('CREATE INDEX IF NOT EXISTS idx_behavior_insights_type ON behavior_insights(type)');
      await client.query('CREATE INDEX IF NOT EXISTS idx_behavior_insights_severity ON behavior_insights(severity)');
      await client.query('CREATE INDEX IF NOT EXISTS idx_anomaly_alerts_address ON anomaly_alerts(address, chain)');
      await client.query('CREATE INDEX IF NOT EXISTS idx_anomaly_alerts_type ON anomaly_alerts(anomaly_type)');
      await client.query('CREATE INDEX IF NOT EXISTS idx_anomaly_alerts_severity ON anomaly_alerts(severity)');
      await client.query('CREATE INDEX IF NOT EXISTS idx_anomaly_alerts_status ON anomaly_alerts(status)');
      await client.query('CREATE INDEX IF NOT EXISTS idx_market_impact_hash ON market_impact_analysis(transaction_hash)');
      await client.query('CREATE INDEX IF NOT EXISTS idx_intelligence_reports_type ON intelligence_reports(report_type)');
      await client.query('CREATE INDEX IF NOT EXISTS idx_intelligence_reports_priority ON intelligence_reports(priority)');
      await client.query('CREATE INDEX IF NOT EXISTS idx_intelligence_reports_generated ON intelligence_reports(generated_at)');
      await client.query('CREATE INDEX IF NOT EXISTS idx_entity_relationships_source ON entity_relationships(source_entity_id)');
      await client.query('CREATE INDEX IF NOT EXISTS idx_entity_relationships_target ON entity_relationships(target_entity_id)');
      await client.query('CREATE INDEX IF NOT EXISTS idx_pattern_cache_address ON pattern_detection_cache(address, chain)');
      await client.query('CREATE INDEX IF NOT EXISTS idx_pattern_cache_expires ON pattern_detection_cache(expires_at)');
      await client.query('CREATE INDEX IF NOT EXISTS idx_analysis_jobs_status ON analysis_jobs(status)');
      await client.query('CREATE INDEX IF NOT EXISTS idx_analysis_jobs_type ON analysis_jobs(job_type)');
      await client.query('CREATE INDEX IF NOT EXISTS idx_analysis_jobs_priority ON analysis_jobs(priority)');

      // ===== Analysis System Tables =====

      // Entity Information Table
      await client.query(`
        CREATE TABLE IF NOT EXISTS entities (
          id SERIAL PRIMARY KEY,
          entity_id VARCHAR(64) UNIQUE NOT NULL,
          name VARCHAR(200),
          type VARCHAR(50) NOT NULL,
          risk_score INTEGER DEFAULT 0,
          confidence DECIMAL(3,2) DEFAULT 0.00,
          metadata JSONB,
          created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
          updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
      `);

      // Entity Addresses Mapping Table
      await client.query(`
        CREATE TABLE IF NOT EXISTS entity_addresses (
          id SERIAL PRIMARY KEY,
          entity_id VARCHAR(64) NOT NULL,
          address VARCHAR(42) NOT NULL,
          chain VARCHAR(20) NOT NULL,
          confidence DECIMAL(3,2) DEFAULT 1.00,
          added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
          FOREIGN KEY (entity_id) REFERENCES entities(entity_id) ON DELETE CASCADE,
          UNIQUE(entity_id, address, chain)
        )
      `);

      // Address Profiles Table
      await client.query(`
        CREATE TABLE IF NOT EXISTS address_profiles (
          id SERIAL PRIMARY KEY,
          address VARCHAR(42) NOT NULL,
          chain VARCHAR(20) NOT NULL,
          entity_id VARCHAR(64),
          risk_score INTEGER DEFAULT 0,
          total_transactions INTEGER DEFAULT 0,
          total_volume_usd DECIMAL(20,2) DEFAULT 0.00,
          first_seen TIMESTAMP,
          last_active TIMESTAMP,
          activity_score DECIMAL(5,2) DEFAULT 0.00,
          metadata JSONB,
          created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
          updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
          UNIQUE(address, chain)
        )
      `);

      // Transaction Patterns Table
      await client.query(`
        CREATE TABLE IF NOT EXISTS transaction_patterns (
          id SERIAL PRIMARY KEY,
          pattern_id VARCHAR(64) UNIQUE NOT NULL,
          address VARCHAR(42) NOT NULL,
          chain VARCHAR(20) NOT NULL,
          pattern_type VARCHAR(50) NOT NULL,
          confidence DECIMAL(3,2) NOT NULL,
          description TEXT,
          frequency INTEGER DEFAULT 1,
          avg_amount_usd DECIMAL(20,2),
          metadata JSONB,
          detected_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
          last_seen TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
      `);

      // Behavior Insights Table
      await client.query(`
        CREATE TABLE IF NOT EXISTS behavior_insights (
          id SERIAL PRIMARY KEY,
          insight_id VARCHAR(64) UNIQUE NOT NULL,
          address VARCHAR(42) NOT NULL,
          type VARCHAR(50) NOT NULL,
          title VARCHAR(200) NOT NULL,
          description TEXT,
          severity VARCHAR(20) DEFAULT 'low',
          confidence DECIMAL(3,2) NOT NULL,
          metadata JSONB,
          created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
      `);

      // Anomaly Alerts Table
      await client.query(`
        CREATE TABLE IF NOT EXISTS anomaly_alerts (
          id SERIAL PRIMARY KEY,
          alert_id VARCHAR(64) UNIQUE NOT NULL,
          address VARCHAR(42) NOT NULL,
          chain VARCHAR(20) NOT NULL,
          anomaly_type VARCHAR(50) NOT NULL,
          severity VARCHAR(20) NOT NULL,
          title VARCHAR(200) NOT NULL,
          description TEXT,
          threshold_value DECIMAL(20,2),
          actual_value DECIMAL(20,2),
          confidence DECIMAL(3,2) NOT NULL,
          status VARCHAR(20) DEFAULT 'active',
          metadata JSONB,
          detected_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
          resolved_at TIMESTAMP
        )
      `);

      // Market Impact Analysis Table
      await client.query(`
        CREATE TABLE IF NOT EXISTS market_impact_analysis (
          id SERIAL PRIMARY KEY,
          transaction_hash VARCHAR(66) NOT NULL,
          impact_score DECIMAL(5,2) NOT NULL,
          liquidity_impact DECIMAL(10,2),
          price_impact_percentage DECIMAL(8,4),
          volume_impact DECIMAL(20,2),
          market_sentiment VARCHAR(20),
          analysis_metadata JSONB,
          analyzed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
      `);

      // Intelligence Reports Table
      await client.query(`
        CREATE TABLE IF NOT EXISTS intelligence_reports (
          id SERIAL PRIMARY KEY,
          report_id VARCHAR(64) UNIQUE NOT NULL,
          title VARCHAR(300) NOT NULL,
          summary TEXT,
          content TEXT,
          report_type VARCHAR(50) NOT NULL,
          priority VARCHAR(20) DEFAULT 'medium',
          tags TEXT[],
          entities VARCHAR(64)[],
          addresses VARCHAR(42)[],
          chains VARCHAR(20)[],
          metadata JSONB,
          generated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
          expires_at TIMESTAMP
        )
      `);

      // Entity Relationships Table
      await client.query(`
        CREATE TABLE IF NOT EXISTS entity_relationships (
          id SERIAL PRIMARY KEY,
          source_entity_id VARCHAR(64) NOT NULL,
          target_entity_id VARCHAR(64) NOT NULL,
          relationship_type VARCHAR(50) NOT NULL,
          strength DECIMAL(3,2) DEFAULT 0.50,
          evidence_count INTEGER DEFAULT 1,
          first_observed TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
          last_observed TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
          metadata JSONB,
          FOREIGN KEY (source_entity_id) REFERENCES entities(entity_id) ON DELETE CASCADE,
          FOREIGN KEY (target_entity_id) REFERENCES entities(entity_id) ON DELETE CASCADE,
          UNIQUE(source_entity_id, target_entity_id, relationship_type)
        )
      `);

      // Pattern Detection Cache Table
      await client.query(`
        CREATE TABLE IF NOT EXISTS pattern_detection_cache (
          id SERIAL PRIMARY KEY,
          cache_key VARCHAR(128) UNIQUE NOT NULL,
          address VARCHAR(42) NOT NULL,
          chain VARCHAR(20) NOT NULL,
          pattern_data JSONB NOT NULL,
          expires_at TIMESTAMP NOT NULL,
          created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
      `);

      // Analysis Job Queue Table
      await client.query(`
        CREATE TABLE IF NOT EXISTS analysis_jobs (
          id SERIAL PRIMARY KEY,
          job_id VARCHAR(64) UNIQUE NOT NULL,
          job_type VARCHAR(50) NOT NULL,
          target_address VARCHAR(42),
          target_transaction VARCHAR(66),
          priority INTEGER DEFAULT 5,
          status VARCHAR(20) DEFAULT 'pending',
          progress INTEGER DEFAULT 0,
          result JSONB,
          error_message TEXT,
          attempts INTEGER DEFAULT 0,
          max_attempts INTEGER DEFAULT 3,
          created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
          started_at TIMESTAMP,
          completed_at TIMESTAMP
        )
      `);

      // Create indexes for analysis tables
      await client.query(`
        CREATE INDEX IF NOT EXISTS idx_entities_type ON entities(type);
        CREATE INDEX IF NOT EXISTS idx_entities_risk_score ON entities(risk_score);
        CREATE INDEX IF NOT EXISTS idx_entity_addresses_address ON entity_addresses(address);
        CREATE INDEX IF NOT EXISTS idx_entity_addresses_chain ON entity_addresses(chain);
        CREATE INDEX IF NOT EXISTS idx_address_profiles_address ON address_profiles(address, chain);
        CREATE INDEX IF NOT EXISTS idx_address_profiles_entity ON address_profiles(entity_id);
        CREATE INDEX IF NOT EXISTS idx_address_profiles_risk ON address_profiles(risk_score);
        CREATE INDEX IF NOT EXISTS idx_transaction_patterns_address ON transaction_patterns(address, chain);
        CREATE INDEX IF NOT EXISTS idx_transaction_patterns_type ON transaction_patterns(pattern_type);
        CREATE INDEX IF NOT EXISTS idx_behavior_insights_address ON behavior_insights(address);
        CREATE INDEX IF NOT EXISTS idx_behavior_insights_type ON behavior_insights(type);
        CREATE INDEX IF NOT EXISTS idx_behavior_insights_severity ON behavior_insights(severity);
        CREATE INDEX IF NOT EXISTS idx_anomaly_alerts_address ON anomaly_alerts(address, chain);
        CREATE INDEX IF NOT EXISTS idx_anomaly_alerts_type ON anomaly_alerts(anomaly_type);
        CREATE INDEX IF NOT EXISTS idx_anomaly_alerts_severity ON anomaly_alerts(severity);
        CREATE INDEX IF NOT EXISTS idx_anomaly_alerts_status ON anomaly_alerts(status);
        CREATE INDEX IF NOT EXISTS idx_market_impact_hash ON market_impact_analysis(transaction_hash);
        CREATE INDEX IF NOT EXISTS idx_intelligence_reports_type ON intelligence_reports(report_type);
        CREATE INDEX IF NOT EXISTS idx_intelligence_reports_priority ON intelligence_reports(priority);
        CREATE INDEX IF NOT EXISTS idx_intelligence_reports_generated ON intelligence_reports(generated_at);
        CREATE INDEX IF NOT EXISTS idx_entity_relationships_source ON entity_relationships(source_entity_id);
        CREATE INDEX IF NOT EXISTS idx_entity_relationships_target ON entity_relationships(target_entity_id);
        CREATE INDEX IF NOT EXISTS idx_pattern_cache_address ON pattern_detection_cache(address, chain);
        CREATE INDEX IF NOT EXISTS idx_pattern_cache_expires ON pattern_detection_cache(expires_at);
        CREATE INDEX IF NOT EXISTS idx_analysis_jobs_status ON analysis_jobs(status);
        CREATE INDEX IF NOT EXISTS idx_analysis_jobs_type ON analysis_jobs(job_type);
        CREATE INDEX IF NOT EXISTS idx_analysis_jobs_priority ON analysis_jobs(priority);
      `);

      logger.info('All database tables initialized successfully');
    } finally {
      client.release();
    }
  }

  // 插入默认配置
  private async insertDefaultConfigs(client: any): Promise<void> {
    const defaultConfigs = [
      // 监控配置
      {
        key: 'monitoring.minTransactionValue',
        value: '50000',
        type: 'number',
        description: '最小监控交易金额 (USD)',
        category: 'monitoring'
      },
      {
        key: 'monitoring.maxTransactionsPerBlock',
        value: '20',
        type: 'number',
        description: '每区块最大处理交易数',
        category: 'monitoring'
      },
      {
        key: 'monitoring.blockCheckInterval',
        value: '15000',
        type: 'number',
        description: '区块检查间隔 (毫秒)',
        category: 'monitoring'
      },
      // 链配置
      {
        key: 'chains.ethereum.rpcUrl',
        value: 'https://ethereum.publicnode.com',
        type: 'string',
        description: 'Ethereum RPC节点地址',
        category: 'chains'
      },
      {
        key: 'chains.bsc.rpcUrl',
        value: 'https://bsc-dataseed1.binance.org',
        type: 'string',
        description: 'BSC RPC节点地址',
        category: 'chains'
      },
      {
        key: 'chains.base.rpcUrl',
        value: 'https://mainnet.base.org',
        type: 'string',
        description: 'Base RPC节点地址',
        category: 'chains'
      },
      {
        key: 'chains.bitcoin.apiUrl',
        value: 'https://blockstream.info/api',
        type: 'string',
        description: 'Bitcoin API地址',
        category: 'chains'
      },
      {
        key: 'chains.solana.rpcUrl',
        value: 'https://api.mainnet-beta.solana.com',
        type: 'string',
        description: 'Solana RPC节点地址',
        category: 'chains'
      },
      // 告警配置
      {
        key: 'alerts.email.enabled',
        value: 'false',
        type: 'boolean',
        description: '启用邮件告警',
        category: 'alerts'
      },
      {
        key: 'alerts.email.host',
        value: 'smtp.gmail.com',
        type: 'string',
        description: 'SMTP服务器地址',
        category: 'alerts'
      },
      {
        key: 'alerts.email.port',
        value: '587',
        type: 'number',
        description: 'SMTP服务器端口',
        category: 'alerts'
      },
      {
        key: 'alerts.webhook.enabled',
        value: 'false',
        type: 'boolean',
        description: '启用Webhook告警',
        category: 'alerts'
      }
    ];

    for (const config of defaultConfigs) {
      await client.query(`
        INSERT INTO system_config (config_key, config_value, config_type, description, category)
        VALUES ($1, $2, $3, $4, $5)
        ON CONFLICT (config_key) DO NOTHING
      `, [config.key, config.value, config.type, config.description, config.category]);
    }

    logger.info('默认配置插入完成');
  }

  // 保存交易
  public async saveTransaction(transaction: Transaction): Promise<void> {
    const client = await this.pool.connect();
    
    try {
      await client.query(`
        INSERT INTO transactions (
          id, hash, chain, from_address, to_address, value, value_usd,
          block_number, timestamp, gas_price, gas_used, token_address,
          token_symbol, token_decimals, confirmed
        ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15)
        ON CONFLICT (id) DO UPDATE SET
          value_usd = EXCLUDED.value_usd,
          confirmed = EXCLUDED.confirmed,
          gas_used = EXCLUDED.gas_used
      `, [
        transaction.id,
        transaction.hash,
        transaction.chain,
        transaction.from,
        transaction.to,
        transaction.value,
        transaction.valueUSD,
        transaction.blockNumber,
        transaction.timestamp,
        transaction.gasPrice,
        transaction.gasUsed,
        transaction.tokenAddress,
        transaction.tokenSymbol,
        transaction.tokenDecimals,
        transaction.confirmed
      ]);

      logger.debug(`已保存交易: ${transaction.hash}`);
    } finally {
      client.release();
    }
  }

  // Get transaction by ID
  public async getTransactionById(id: string): Promise<Transaction | null> {
    const client = await this.pool.connect();
    
    try {
      const result = await client.query(
        'SELECT * FROM transactions WHERE id = $1',
        [id]
      );

      if (result.rows.length === 0) {
        return null;
      }

      const row = result.rows[0];
      return {
        id: row.id,
        hash: row.hash,
        chain: row.chain as ChainType,
        from: row.from_address,
        to: row.to_address,
        value: row.value,
        valueUSD: parseFloat(row.value_usd),
        blockNumber: parseInt(row.block_number),
        timestamp: new Date(row.timestamp),
        gasPrice: row.gas_price,
        gasUsed: row.gas_used ? parseInt(row.gas_used) : undefined,
        tokenAddress: row.token_address,
        tokenSymbol: row.token_symbol,
        tokenDecimals: row.token_decimals ? parseInt(row.token_decimals) : undefined,
        confirmed: row.confirmed,
        fromLabel: row.from_label ? JSON.parse(row.from_label) : undefined,
        toLabel: row.to_label ? JSON.parse(row.to_label) : undefined
      };
    } finally {
      client.release();
    }
  }



  // Get recent transactions by address (for anomaly detection)
  public async getRecentTransactionsByAddress(address: string, chain: ChainType, hours: number): Promise<any[]> {
    const client = await this.pool.connect();
    try {
      const hoursAgo = new Date(Date.now() - hours * 60 * 60 * 1000);
      const result = await client.query(
        'SELECT * FROM transactions WHERE (from_address = $1 OR to_address = $1) AND chain = $2 AND timestamp >= $3 ORDER BY timestamp DESC',
        [address.toLowerCase(), chain, hoursAgo]
      );
      
      return result.rows.map(row => ({
        id: row.id,
        hash: row.hash,
        chain: row.chain,
        fromAddress: row.from_address,
        toAddress: row.to_address,
        value: parseFloat(row.value),
        valueUsd: parseFloat(row.value_usd),
        tokenAddress: row.token_address,
        tokenSymbol: row.token_symbol,
        tokenDecimals: row.token_decimals,
        blockNumber: row.block_number,
        timestamp: row.timestamp,
        fromLabel: row.from_label,
        toLabel: row.to_label
      }));
    } finally {
      client.release();
    }
  }

  // ===== Analysis Database Operations =====

  // Entity operations
  public async createEntity(entity: any): Promise<string> {
    const client = await this.pool.connect();
    try {
      const result = await client.query(`
        INSERT INTO entities (entity_id, name, type, risk_score, confidence, metadata)
        VALUES ($1, $2, $3, $4, $5, $6)
        RETURNING entity_id
      `, [entity.id, entity.name, entity.type, entity.riskScore, entity.confidence, JSON.stringify(entity.metadata || {})]);
      
      return result.rows[0].entity_id;
    } finally {
      client.release();
    }
  }

  public async getEntityById(entityId: string): Promise<any | null> {
    const client = await this.pool.connect();
    try {
      const result = await client.query('SELECT * FROM entities WHERE entity_id = $1', [entityId]);
      if (result.rows.length === 0) return null;

      const entity = result.rows[0];
      return {
        id: entity.entity_id,
        name: entity.name,
        type: entity.type,
        riskScore: entity.risk_score,
        confidence: parseFloat(entity.confidence),
        metadata: entity.metadata,
        lastUpdated: entity.updated_at
      };
    } finally {
      client.release();
    }
  }

  public async updateEntity(entityId: string, updates: any): Promise<void> {
    const client = await this.pool.connect();
    try {
      await client.query(`
        UPDATE entities 
        SET name = $2, type = $3, risk_score = $4, confidence = $5, metadata = $6, updated_at = CURRENT_TIMESTAMP
        WHERE entity_id = $1
      `, [entityId, updates.name, updates.type, updates.riskScore, updates.confidence, JSON.stringify(updates.metadata || {})]);
    } finally {
      client.release();
    }
  }

  // Entity address mapping operations
  public async addEntityAddress(entityId: string, address: string, chain: ChainType, confidence: number = 1.0): Promise<void> {
    const client = await this.pool.connect();
    try {
      await client.query(`
        INSERT INTO entity_addresses (entity_id, address, chain, confidence)
        VALUES ($1, $2, $3, $4)
        ON CONFLICT (entity_id, address, chain) DO UPDATE SET confidence = $4, added_at = CURRENT_TIMESTAMP
      `, [entityId, address.toLowerCase(), chain, confidence]);
    } finally {
      client.release();
    }
  }

  public async getEntityAddresses(entityId: string): Promise<any[]> {
    const client = await this.pool.connect();
    try {
      const result = await client.query('SELECT * FROM entity_addresses WHERE entity_id = $1', [entityId]);
      return result.rows.map(row => ({
        address: row.address,
        chain: row.chain,
        confidence: parseFloat(row.confidence),
        addedAt: row.added_at
      }));
    } finally {
      client.release();
    }
  }

  // Address profile operations
  public async createOrUpdateAddressProfile(profile: any): Promise<void> {
    const client = await this.pool.connect();
    try {
      await client.query(`
        INSERT INTO address_profiles (
          address, chain, entity_id, risk_score, total_transactions, 
          total_volume_usd, first_seen, last_active, activity_score, metadata
        )
        VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
        ON CONFLICT (address, chain) DO UPDATE SET
          entity_id = $3, risk_score = $4, total_transactions = $5,
          total_volume_usd = $6, last_active = $8, activity_score = $9,
          metadata = $10, updated_at = CURRENT_TIMESTAMP
      `, [
        profile.address.toLowerCase(), profile.chain, profile.entityId,
        profile.riskScore, profile.totalTransactions, profile.totalVolumeUsd,
        profile.firstSeen, profile.lastActive, profile.activityScore,
        JSON.stringify(profile.metadata || {})
      ]);
    } finally {
      client.release();
    }
  }

  public async getAddressProfile(address: string, chain: ChainType): Promise<any | null> {
    const client = await this.pool.connect();
    try {
      const result = await client.query('SELECT * FROM address_profiles WHERE address = $1 AND chain = $2', [address.toLowerCase(), chain]);
      if (result.rows.length === 0) return null;

      const profile = result.rows[0];
      return {
        address: profile.address,
        chain: profile.chain,
        entityId: profile.entity_id,
        riskScore: profile.risk_score,
        totalTransactions: profile.total_transactions,
        totalVolumeUsd: parseFloat(profile.total_volume_usd),
        firstSeen: profile.first_seen,
        lastActive: profile.last_active,
        activityScore: parseFloat(profile.activity_score),
        metadata: profile.metadata
      };
    } finally {
      client.release();
    }
  }

  // Transaction pattern operations
  public async saveTransactionPattern(pattern: any): Promise<void> {
    const client = await this.pool.connect();
    try {
      await client.query(`
        INSERT INTO transaction_patterns (
          pattern_id, address, chain, pattern_type, confidence, description,
          frequency, avg_amount_usd, metadata
        )
        VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)
        ON CONFLICT (pattern_id) DO UPDATE SET
          confidence = $5, description = $6, frequency = $7,
          avg_amount_usd = $8, metadata = $9, last_seen = CURRENT_TIMESTAMP
      `, [
        pattern.id, pattern.address.toLowerCase(), pattern.chain, pattern.type,
        pattern.confidence, pattern.description, pattern.frequency,
        pattern.avgAmount, JSON.stringify(pattern.metadata || {})
      ]);
    } finally {
      client.release();
    }
  }

  public async getTransactionPatterns(address: string, chain: ChainType): Promise<any[]> {
    const client = await this.pool.connect();
    try {
      const result = await client.query(
        'SELECT * FROM transaction_patterns WHERE address = $1 AND chain = $2 ORDER BY detected_at DESC',
        [address.toLowerCase(), chain]
      );
      
      return result.rows.map(row => ({
        id: row.pattern_id,
        type: row.pattern_type,
        confidence: parseFloat(row.confidence),
        description: row.description,
        frequency: row.frequency,
        avgAmount: parseFloat(row.avg_amount_usd),
        detectedAt: row.detected_at,
        lastSeen: row.last_seen,
        metadata: row.metadata
      }));
    } finally {
      client.release();
    }
  }

  // Behavior insights operations
  public async saveBehaviorInsight(insight: any): Promise<void> {
    const client = await this.pool.connect();
    try {
      await client.query(`
        INSERT INTO behavior_insights (insight_id, address, type, title, description, severity, confidence, metadata)
        VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
        ON CONFLICT (insight_id) DO UPDATE SET
          title = $4, description = $5, severity = $6, confidence = $7, metadata = $8
      `, [
        insight.id, insight.address.toLowerCase(), insight.type, insight.title,
        insight.description, insight.severity, insight.confidence,
        JSON.stringify(insight.metadata || {})
      ]);
    } finally {
      client.release();
    }
  }

  public async getBehaviorInsights(address: string): Promise<any[]> {
    const client = await this.pool.connect();
    try {
      const result = await client.query(
        'SELECT * FROM behavior_insights WHERE address = $1 ORDER BY created_at DESC',
        [address.toLowerCase()]
      );
      
      return result.rows.map(row => ({
        id: row.insight_id,
        address: row.address,
        type: row.type,
        title: row.title,
        description: row.description,
        severity: row.severity,
        confidence: parseFloat(row.confidence),
        timestamp: row.created_at,
        metadata: row.metadata
      }));
    } finally {
      client.release();
    }
  }

  // Anomaly alert operations
  public async saveAnomalyAlert(alert: any): Promise<void> {
    const client = await this.pool.connect();
    try {
      await client.query(`
        INSERT INTO anomaly_alerts (
          alert_id, address, chain, anomaly_type, severity, title, description,
          threshold_value, actual_value, confidence, status, metadata
        )
        VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12)
        ON CONFLICT (alert_id) DO UPDATE SET
          severity = $5, title = $6, description = $7, status = $11, metadata = $12
      `, [
        alert.id, alert.address.toLowerCase(), alert.chain, alert.type,
        alert.severity, alert.title, alert.description, alert.threshold,
        alert.actualValue, alert.confidence, alert.status || 'active',
        JSON.stringify(alert.metadata || {})
      ]);
    } finally {
      client.release();
    }
  }

  public async getAnomalyAlerts(address?: string, chain?: ChainType, status?: string): Promise<any[]> {
    const client = await this.pool.connect();
    try {
      let query = 'SELECT * FROM anomaly_alerts WHERE 1=1';
      const params: any[] = [];
      let paramIndex = 1;

      if (address) {
        query += ` AND address = $${paramIndex}`;
        params.push(address.toLowerCase());
        paramIndex++;
      }

      if (chain) {
        query += ` AND chain = $${paramIndex}`;
        params.push(chain);
        paramIndex++;
      }

      if (status) {
        query += ` AND status = $${paramIndex}`;
        params.push(status);
        paramIndex++;
      }

      query += ' ORDER BY detected_at DESC';

      const result = await client.query(query, params);
      
      return result.rows.map(row => ({
        id: row.alert_id,
        address: row.address,
        chain: row.chain,
        type: row.anomaly_type,
        severity: row.severity,
        title: row.title,
        description: row.description,
        threshold: parseFloat(row.threshold_value),
        actualValue: parseFloat(row.actual_value),
        confidence: parseFloat(row.confidence),
        status: row.status,
        detectedAt: row.detected_at,
        resolvedAt: row.resolved_at,
        metadata: row.metadata
      }));
    } finally {
      client.release();
    }
  }

  // Market impact analysis operations
  public async saveMarketImpactAnalysis(analysis: any): Promise<void> {
    const client = await this.pool.connect();
    try {
      await client.query(`
        INSERT INTO market_impact_analysis (
          transaction_hash, impact_score, liquidity_impact, price_impact_percentage,
          volume_impact, market_sentiment, analysis_metadata
        )
        VALUES ($1, $2, $3, $4, $5, $6, $7)
        ON CONFLICT (transaction_hash) DO UPDATE SET
          impact_score = $2, liquidity_impact = $3, price_impact_percentage = $4,
          volume_impact = $5, market_sentiment = $6, analysis_metadata = $7
      `, [
        analysis.transactionHash, analysis.impactScore, analysis.liquidityImpact,
        analysis.priceImpactPercentage, analysis.volumeImpact, analysis.marketSentiment,
        JSON.stringify(analysis.metadata || {})
      ]);
    } finally {
      client.release();
    }
  }

  public async getMarketImpactAnalysis(transactionHash: string): Promise<any | null> {
    const client = await this.pool.connect();
    try {
      const result = await client.query('SELECT * FROM market_impact_analysis WHERE transaction_hash = $1', [transactionHash]);
      if (result.rows.length === 0) return null;

      const analysis = result.rows[0];
      return {
        transactionHash: analysis.transaction_hash,
        impactScore: parseFloat(analysis.impact_score),
        liquidityImpact: parseFloat(analysis.liquidity_impact),
        priceImpactPercentage: parseFloat(analysis.price_impact_percentage),
        volumeImpact: parseFloat(analysis.volume_impact),
        marketSentiment: analysis.market_sentiment,
        analyzedAt: analysis.analyzed_at,
        metadata: analysis.analysis_metadata
      };
    } finally {
      client.release();
    }
  }

  // Intelligence report operations
  public async saveIntelligenceReport(report: any): Promise<void> {
    const client = await this.pool.connect();
    try {
      await client.query(`
        INSERT INTO intelligence_reports (
          report_id, title, summary, content, report_type, priority,
          tags, entities, addresses, chains, metadata, expires_at
        )
        VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12)
        ON CONFLICT (report_id) DO UPDATE SET
          title = $2, summary = $3, content = $4, priority = $6,
          tags = $7, entities = $8, addresses = $9, chains = $10, metadata = $11
      `, [
        report.id, report.title, report.summary, report.content, report.type,
        report.priority, report.tags, report.entities, report.addresses,
        report.chains, JSON.stringify(report.metadata || {}), report.expiresAt
      ]);
    } finally {
      client.release();
    }
  }

  public async getIntelligenceReports(type?: string, priority?: string, limit: number = 50): Promise<any[]> {
    const client = await this.pool.connect();
    try {
      let query = 'SELECT * FROM intelligence_reports WHERE 1=1';
      const params: any[] = [];
      let paramIndex = 1;

      if (type) {
        query += ` AND report_type = $${paramIndex}`;
        params.push(type);
        paramIndex++;
      }

      if (priority) {
        query += ` AND priority = $${paramIndex}`;
        params.push(priority);
        paramIndex++;
      }

      query += ` ORDER BY generated_at DESC LIMIT $${paramIndex}`;
      params.push(limit);

      const result = await client.query(query, params);
      
      return result.rows.map(row => ({
        id: row.report_id,
        title: row.title,
        summary: row.summary,
        content: row.content,
        type: row.report_type,
        priority: row.priority,
        tags: row.tags,
        entities: row.entities,
        addresses: row.addresses,
        chains: row.chains,
        generatedAt: row.generated_at,
        expiresAt: row.expires_at,
        metadata: row.metadata
      }));
    } finally {
      client.release();
    }
  }

  // Pattern detection cache operations
  public async getCachedPattern(cacheKey: string): Promise<any | null> {
    const client = await this.pool.connect();
    try {
      const result = await client.query(
        'SELECT * FROM pattern_detection_cache WHERE cache_key = $1 AND expires_at > CURRENT_TIMESTAMP',
        [cacheKey]
      );
      
      if (result.rows.length === 0) return null;
      return result.rows[0].pattern_data;
    } finally {
      client.release();
    }
  }

  public async setCachedPattern(cacheKey: string, address: string, chain: ChainType, data: any, ttlMinutes: number = 60): Promise<void> {
    const client = await this.pool.connect();
    try {
      const expiresAt = new Date(Date.now() + ttlMinutes * 60 * 1000);
      await client.query(`
        INSERT INTO pattern_detection_cache (cache_key, address, chain, pattern_data, expires_at)
        VALUES ($1, $2, $3, $4, $5)
        ON CONFLICT (cache_key) DO UPDATE SET
          pattern_data = $4, expires_at = $5, created_at = CURRENT_TIMESTAMP
      `, [cacheKey, address.toLowerCase(), chain, JSON.stringify(data), expiresAt]);
    } finally {
      client.release();
    }
  }

  // Clean up expired cache entries
  public async cleanupExpiredCache(): Promise<void> {
    const client = await this.pool.connect();
    try {
      await client.query('DELETE FROM pattern_detection_cache WHERE expires_at < CURRENT_TIMESTAMP');
    } finally {
      client.release();
    }
  }

  // 获取交易列表
  public async getTransactions(
    page: number = 1,
    limit: number = 50,
    chain?: string,
    minValue?: number
  ): Promise<{ transactions: Transaction[]; total: number }> {
    const client = await this.pool.connect();
    
    try {
      let whereClause = 'WHERE 1=1';
      const params: any[] = [];
      let paramCount = 0;

      if (chain) {
        whereClause += ` AND chain = $${++paramCount}`;
        params.push(chain);
      }

      if (minValue) {
        whereClause += ` AND value_usd >= $${++paramCount}`;
        params.push(minValue);
      }

      // 获取总数
      const countResult = await client.query(`
        SELECT COUNT(*) as total FROM transactions ${whereClause}
      `, params);
      const total = parseInt(countResult.rows[0].total);

      // 获取分页数据
      const offset = (page - 1) * limit;
      whereClause += ` ORDER BY timestamp DESC LIMIT $${++paramCount} OFFSET $${++paramCount}`;
      params.push(limit, offset);

      const result = await client.query(`
        SELECT * FROM transactions ${whereClause}
      `, params);

      const transactions = result.rows.map((row: any) => ({
        id: row.id,
        hash: row.hash,
        chain: row.chain,
        from: row.from_address,
        to: row.to_address,
        value: row.value,
        valueUSD: parseFloat(row.value_usd),
        blockNumber: parseInt(row.block_number),
        timestamp: new Date(row.timestamp),
        gasPrice: row.gas_price,
        gasUsed: row.gas_used,
        tokenAddress: row.token_address,
        tokenSymbol: row.token_symbol,
        tokenDecimals: row.token_decimals,
        confirmed: row.confirmed
      }));

      return { transactions, total };
    } finally {
      client.release();
    }
  }

  // 根据地址获取交易记录（用于鲸鱼检测）
  public async getTransactionsByAddress(address: string, chain: string): Promise<Transaction[]> {
    const client = await this.pool.connect();
    
    try {
      const result = await client.query(`
        SELECT * FROM transactions 
        WHERE (from_address = $1 OR to_address = $1) 
          AND chain = $2 
        ORDER BY timestamp DESC 
        LIMIT 20
      `, [address, chain]);

      return result.rows.map((row: any) => ({
        id: row.id,
        hash: row.hash,
        chain: row.chain,
        from: row.from_address,
        to: row.to_address,
        value: row.value,
        valueUSD: parseFloat(row.value_usd),
        blockNumber: row.block_number,
        timestamp: new Date(row.timestamp),
        gasPrice: row.gas_price,
        gasUsed: row.gas_used,
        tokenAddress: row.token_address,
        tokenSymbol: row.token_symbol,
        tokenDecimals: row.token_decimals,
        confirmed: row.confirmed
      }));
    } finally {
      client.release();
    }
  }

  // 保存地址标签
  public async saveAddressLabel(label: AddressLabel): Promise<void> {
    const client = await this.pool.connect();
    
    try {
      await client.query(`
        INSERT INTO address_labels (address, label, type, chain)
        VALUES ($1, $2, $3, $4)
        ON CONFLICT (address, chain) DO UPDATE SET
          label = EXCLUDED.label,
          type = EXCLUDED.type
      `, [label.address, label.label, label.type, label.chain]);

      logger.debug(`已保存地址标签: ${label.address} - ${label.label}`);
    } finally {
      client.release();
    }
  }

  // 获取地址标签
  public async getAddressLabel(address: string, chain: string): Promise<AddressLabel | null> {
    const client = await this.pool.connect();
    
    try {
      const result = await client.query(`
        SELECT * FROM address_labels WHERE address = $1 AND chain = $2
      `, [address, chain]);

      if (result.rows.length === 0) {
        return null;
      }

      const row = result.rows[0];
      return {
        address: row.address,
        label: row.label,
        type: row.type,
        chain: row.chain,
        createdAt: new Date(row.created_at)
      };
    } finally {
      client.release();
    }
  }

  // 保存告警消息
  public async saveAlertMessage(alertMessage: AlertMessage): Promise<void> {
    const client = await this.pool.connect();
    
    try {
      await client.query(`
        INSERT INTO alert_messages (
          id, transaction_id, config_id, channels, sent, sent_at, error
        ) VALUES ($1, $2, $3, $4, $5, $6, $7)
      `, [
        alertMessage.id,
        alertMessage.transaction.id,
        alertMessage.configId,
        alertMessage.channels,
        alertMessage.sent,
        alertMessage.sentAt,
        alertMessage.error
      ]);

      logger.debug(`已保存告警消息: ${alertMessage.id}`);
    } finally {
      client.release();
    }
  }

  // 获取统计数据
  public async getStatistics(): Promise<{
    totalTransactions: number;
    totalValueUSD: number;
    todayTransactions: number;
    todayValueUSD: number;
    averageValueUSD: number;
    transactionsByChain: Record<string, number>;
    valueByChain: Record<string, number>;
    recentTransactions: Transaction[];
    alertsToday: number;
  }> {
    const client = await this.pool.connect();
    
    try {
      // 总交易数和总金额
      const totalResult = await client.query(`
        SELECT COUNT(*) as count, SUM(value_usd) as total_value
        FROM transactions
      `);
      const totalTransactions = parseInt(totalResult.rows[0].count);
      const totalValueUSD = parseFloat(totalResult.rows[0].total_value || '0');

      // 今日交易统计（UTC时间）
      const todayResult = await client.query(`
        SELECT COUNT(*) as count, SUM(value_usd) as total_value
        FROM transactions
        WHERE DATE(timestamp) = CURRENT_DATE
      `);
      const todayTransactions = parseInt(todayResult.rows[0].count);
      const todayValueUSD = parseFloat(todayResult.rows[0].total_value || '0');

      // 计算平均交易金额
      const averageValueUSD = totalTransactions > 0 ? totalValueUSD / totalTransactions : 0;

      // 按链统计交易数
      const chainCountResult = await client.query(`
        SELECT chain, COUNT(*) as count
        FROM transactions
        GROUP BY chain
      `);
      const transactionsByChain: Record<string, number> = {};
      chainCountResult.rows.forEach((row: any) => {
        transactionsByChain[row.chain] = parseInt(row.count);
      });

      // 按链统计金额
      const chainValueResult = await client.query(`
        SELECT chain, SUM(value_usd) as total_value
        FROM transactions
        GROUP BY chain
      `);
      const valueByChain: Record<string, number> = {};
      chainValueResult.rows.forEach((row: any) => {
        valueByChain[row.chain] = parseFloat(row.total_value || '0');
      });

      // 最近交易
      const recentResult = await client.query(`
        SELECT * FROM transactions
        ORDER BY timestamp DESC
        LIMIT 10
      `);
      const recentTransactions = recentResult.rows.map((row: any) => ({
        id: row.id,
        hash: row.hash,
        chain: row.chain,
        from: row.from_address,
        to: row.to_address,
        value: row.value,
        valueUSD: parseFloat(row.value_usd),
        blockNumber: row.block_number,
        timestamp: new Date(row.timestamp),
        gasPrice: row.gas_price,
        gasUsed: row.gas_used,
        tokenAddress: row.token_address,
        tokenSymbol: row.token_symbol,
        tokenDecimals: row.token_decimals,
        confirmed: row.confirmed
      }));

      // 今日告警数（简化实现）
      const alertsToday = 0; // TODO: 实现真实的告警统计

      return {
        totalTransactions,
        totalValueUSD,
        todayTransactions,
        todayValueUSD,
        averageValueUSD,
        transactionsByChain,
        valueByChain,
        recentTransactions,
        alertsToday
      };
    } finally {
      client.release();
    }
  }

  // 关闭数据库连接
  public async close(): Promise<void> {
    await this.pool.end();
    logger.info('数据库连接已关闭');
  }

  // ==================== 系统配置管理 ====================

  // 获取配置值
  public async getConfig(key: string): Promise<any> {
    const client = await this.pool.connect();
    
    try {
      const result = await client.query(
        'SELECT config_value, config_type FROM system_config WHERE config_key = $1',
        [key]
      );
      
      if (result.rows.length === 0) {
        return null;
      }
      
      const { config_value, config_type } = result.rows[0];
      return this.parseConfigValue(config_value, config_type);
    } finally {
      client.release();
    }
  }

  // 获取分类配置
  public async getConfigsByCategory(category: string): Promise<Record<string, any>> {
    const client = await this.pool.connect();
    
    try {
      const result = await client.query(
        'SELECT config_key, config_value, config_type FROM system_config WHERE category = $1',
        [category]
      );
      
      const configs: Record<string, any> = {};
      result.rows.forEach((row: any) => {
        const key = row.config_key.replace(`${category}.`, '');
        configs[key] = this.parseConfigValue(row.config_value, row.config_type);
      });
      
      return configs;
    } finally {
      client.release();
    }
  }

  // 获取所有配置
  public async getAllConfigs(): Promise<Record<string, any>> {
    const client = await this.pool.connect();
    
    try {
      const result = await client.query(
        'SELECT config_key, config_value, config_type, description, category FROM system_config ORDER BY category, config_key'
      );
      
      const configs: Record<string, any> = {};
      result.rows.forEach((row: any) => {
        configs[row.config_key] = {
          value: this.parseConfigValue(row.config_value, row.config_type),
          type: row.config_type,
          description: row.description,
          category: row.category
        };
      });
      
      return configs;
    } finally {
      client.release();
    }
  }

  // 设置配置值
  public async setConfig(key: string, value: any, type?: string): Promise<void> {
    const client = await this.pool.connect();
    
    try {
      const configValue = this.stringifyConfigValue(value);
      const configType = type || this.inferConfigType(value);
      
      await client.query(`
        INSERT INTO system_config (config_key, config_value, config_type, updated_at)
        VALUES ($1, $2, $3, CURRENT_TIMESTAMP)
        ON CONFLICT (config_key) 
        DO UPDATE SET 
          config_value = EXCLUDED.config_value,
          config_type = EXCLUDED.config_type,
          updated_at = CURRENT_TIMESTAMP
      `, [key, configValue, configType]);
      
      logger.info(`配置已更新: ${key} = ${configValue}`);
    } finally {
      client.release();
    }
  }

  // 批量设置配置
  public async setConfigs(configs: Record<string, any>): Promise<void> {
    const client = await this.pool.connect();
    
    try {
      await client.query('BEGIN');
      
      for (const [key, value] of Object.entries(configs)) {
        const configValue = this.stringifyConfigValue(value);
        const configType = this.inferConfigType(value);
        
        await client.query(`
          INSERT INTO system_config (config_key, config_value, config_type, updated_at)
          VALUES ($1, $2, $3, CURRENT_TIMESTAMP)
          ON CONFLICT (config_key) 
          DO UPDATE SET 
            config_value = EXCLUDED.config_value,
            config_type = EXCLUDED.config_type,
            updated_at = CURRENT_TIMESTAMP
        `, [key, configValue, configType]);
      }
      
      await client.query('COMMIT');
      logger.info(`批量更新配置完成，共 ${Object.keys(configs).length} 项`);
    } catch (error) {
      await client.query('ROLLBACK');
      throw error;
    } finally {
      client.release();
    }
  }

  // 删除配置
  public async deleteConfig(key: string): Promise<void> {
    const client = await this.pool.connect();
    
    try {
      await client.query('DELETE FROM system_config WHERE config_key = $1', [key]);
      logger.info(`配置已删除: ${key}`);
    } finally {
      client.release();
    }
  }

  // 解析配置值
  private parseConfigValue(value: string, type: string): any {
    switch (type) {
      case 'boolean':
        return value === 'true';
      case 'number':
        return parseFloat(value);
      case 'json':
        return JSON.parse(value);
      case 'array':
        return JSON.parse(value);
      default:
        return value;
    }
  }

  // 字符串化配置值
  private stringifyConfigValue(value: any): string {
    if (typeof value === 'object') {
      return JSON.stringify(value);
    }
    return String(value);
  }

  // 推断配置类型
  private inferConfigType(value: any): string {
    if (typeof value === 'boolean') return 'boolean';
    if (typeof value === 'number') return 'number';
    if (Array.isArray(value)) return 'array';
    if (typeof value === 'object') return 'json';
    return 'string';
  }
} 